IF EXISTS (SELECT * FROM SYSOBJECTS WHERE name='spRpt_CartulinasVendidasPorPlaca' and XTYPE='P')
BEGIN
	DROP PROCEDURE [dbo].[spRpt_CartulinasVendidasPorPlaca]
END
GO
CREATE PROCEDURE [dbo].[spRpt_CartulinasVendidasPorPlaca]
	 @OP VARCHAR(64)=NULL
	,@ParamAuxiliar NVARCHAR(MAX)=NULL   /* PARAMETRO AUXILIAR */
	,@Fecha_Ini DATE = NULL
	,@Fecha_Fin DATE = NULL
WITH ENCRYPTION
AS
SET NOCOUNT ON
BEGIN TRY

--DECLARE @AUX VARCHAR(2048)
DECLARE @result TABLE(Placa VARCHAR(8),Cartulinas VARCHAR(1024),BORRAR BIT DEFAULT 1 NOT NULL)


/*****     Imprimir    *****/
IF @OP = 'Imprimir'
BEGIN
	
	insert into @result
		(
		Placa,
		Cartulinas
		)
	select 
		r.CodPlaca,
		STUFF((
			SELECT		',' + CONVERT(VARCHAR(8),c2.CodCartulina)
			FROM		Cartulina c2
			WHERE		c2.CodRecaudo=r.CodRecaudo AND CONVERT(DATE,c2.FechaGrab) BETWEEN @Fecha_Ini AND @Fecha_Fin			
			FOR XML PATH(''), TYPE
		).value('.','NVARCHAR(1024)'),1,1,'')
	from 
		Recaudo r
	WHERE
		CONVERT(DATE,r.FechaGrab) BETWEEN @Fecha_Ini AND @Fecha_Fin
	group by
		r.CodPlaca,
		r.CodRecaudo
	ORDER BY
		r.CodRecaudo	
	
	DELETE FROM @result WHERE Cartulinas IS NULL
	
	insert into @result
		(
		Placa,
		Cartulinas,
		BORRAR
		)
	select 
		res.Placa,
		STUFF((
			SELECT		',' + res2.Cartulinas
			FROM		@result res2
			WHERE		res2.Placa=res.Placa
			FOR XML PATH(''), TYPE
		).value('.','NVARCHAR(1024)'),1,1,''),
		0	
	from 
		@result res
		--inner join Cartulina c
		--	ON r.CodRecaudo=c.CodRecaudo
	group by
		res.Placa
	
	
	DELETE FROM @result WHERE BORRAR=1
	
	SELECT 
		r.Placa AS [PLACA],
		r.Cartulinas AS [CARTULINAS]
	FROM @result r
	
	RETURN 0
END

--- Si se llega a este punto es porque el @OP indicado no Existe en el Procedimiento Almacenado
IF @OP IS NOT NULL
	BEGIN
	SET @ParamAuxiliar='La Operacion "'+@OP+'" No esta definida en el Procedimiento almacenado "'+OBJECT_NAME(@@PROCID)+'", Revise!.'
	RAISERROR(@ParamAuxiliar,16,1)
	RETURN 1
END

--- CONTROLAR Y REGISTRAR LOS ERRORES
END TRY
BEGIN CATCH
	--- Recopilo la informacion del error
	DECLARE @ERROR_NUMBER INT=ERROR_NUMBER(),@ERROR_SEVERITY INT=ERROR_SEVERITY(),@ERROR_STATE INT=ERROR_STATE(),@ERROR_PROCEDURE VARCHAR(128)=ERROR_PROCEDURE(),@ERROR_LINE INT=ERROR_LINE(),@ERROR_MESSAGE VARCHAR(4096)=ERROR_MESSAGE()
	--- Si hay transacciones abiertas, hago un rollback, SE ENCIERRA EN UN (TRY-CATCH) PARA QUE NO SE MUESTRE EL ERROR: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT
	IF(@@TRANCOUNT>0) BEGIN BEGIN TRY ROLLBACK TRANSACTION END TRY BEGIN CATCH END CATCH END
	--- Ejecuto el SP que controla y registra el error
	EXEC [spSeguridadMessageException] @OP='MostrarMensaje',@ERROR_NUMBER=@ERROR_NUMBER,@ERROR_SEVERITY=@ERROR_SEVERITY,@ERROR_STATE=@ERROR_STATE,@ERROR_PROCEDURE=@ERROR_PROCEDURE,@ERROR_LINE=@ERROR_LINE,@ERROR_MESSAGE=@ERROR_MESSAGE
	RETURN 1
END CATCH;
